package be.artesis.titamo.io;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Vector;

import be.artesis.titamo.exceptions.DriverNotFoundException;
import be.artesis.titamo.exceptions.InterfaceNotFoundException;
import be.artesis.titamo.importer.ProgressObservable;
import be.artesis.titamo.io.dbinterface.SQLInterface;

/**
 * This class contains all info regarding a SQL-Database used for importing or retrieving the timetable-data.
 * 
 * @author Sander Eskens
 *
 */
public class SQLDatabase extends ProgressObservable
{
	public final static String[] SUPPORTED_DB_SYSTEMS = {
		"MySQL",
		"PostgreSQL"
	};
	public final static String[] DB_DRIVERS = {
		"com.mysql.jdbc.Driver",
		"org.postgresql.Driver"
	};
	
	private static final String DB_INTERFACE_PACKAGE = SQLInterface.class.getPackage().toString().split(" ")[1];
	
	// CONSTANTS
	private static final int DEFAULT_CURSUSNAAM_SIZE = 200;
	private static final int DEFAULT_AANVANG_SIZE = 5;
	private static final int DEFAULT_KLAS_SIZE = 50;
	private static final int DEFAULT_LOKAAL_SIZE = 50;
	private static final int DEFAULT_DUUR_SIZE = 5;
	private static final int DEFAULT_DOCENT_SIZE = 280;
	private static final int DEFAULT_MAX_ERROR = 200;
	private static final int DEFAULT_MAX_FILEPATH = 200;
	private static final int DEFAULT_MAX_LIJN = 4;
	
	
	private String url;
	private String username;
	private String password;
	private String driver;
	private int dbSystemId;
	private String timetableName;
	private Connection con;
	
	private SQLInterface sqlInterface;
	
	public SQLDatabase(String driver, String url, String uname, String password, String dbTable)
	{
		super();
		url = url.trim();
		for (int i = 0; i < DB_DRIVERS.length; i++) 
			if (DB_DRIVERS[i].equals(driver)) this.dbSystemId = i;
		this.setUrl(url);
		this.setUname(uname);
		this.setPassword(password);
		this.setDriver(driver);
		this.timetableName = dbTable;
	}
	
	private void openConnection() throws SQLException, DriverNotFoundException, InterfaceNotFoundException
	{
		try
		{
			// Loading the driver
			Class.forName(this.driver);
			
			// Loading the SQL-specific interface
			String classpath = DB_INTERFACE_PACKAGE.toString() + "." + SUPPORTED_DB_SYSTEMS[dbSystemId] + "Interface";
			//sSystem.out.println(DB_INTERFACE_PACKAGE.toString());
			this.sqlInterface = (SQLInterface) Class.forName(classpath).newInstance();
			
			// Creating a connection
			con = DriverManager.getConnection(this.url,this.username, this.password);
		}
		catch (ClassNotFoundException e)
		{
			if (e.getMessage().contains(DB_INTERFACE_PACKAGE.toString())) 
				throw new InterfaceNotFoundException("The interface for the " + SUPPORTED_DB_SYSTEMS[dbSystemId] + "-database could not be found.");
			else
				throw new DriverNotFoundException("The driver for the " + SUPPORTED_DB_SYSTEMS[dbSystemId] + "-database could not be found.");
			
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public void testConnection() throws SQLException, DriverNotFoundException, InterfaceNotFoundException
	{
		this.openConnection();
		this.closeConnection();
	}
	
	private void closeConnection() throws SQLException
	{
		this.con.close();
	}
	
	public void setUrl(String url) {
		this.url = url;
	}

	public String getUrl() {
		return url;
	}

	public void setUname(String uname) {
		this.username = uname;
	}

	public String getUname() {
		return username;
	}

	public void setPassword(String password) {
		this.password = password;
	}

	public String getPassword() {
		return password;
	}

	public void setDriver(String driver) {
		this.driver = driver;
	}

	public String getDriver() {
		return driver;
	}
	
	public void createTimeTable(TimeTableDataFile data) throws SQLException, DriverNotFoundException, InterfaceNotFoundException
	{
		int max_aanvang = 0;
		int max_duur = 0;
		int max_docent = 0;
		int max_cursusnaam = 0;
		int max_klas = 0;
		int max_lokaal = 0;
		int max_error = 0;
		int max_filepath = data.getFilepath().length();
		int max_lijn = 5;
		
		data.reset();
		while (data.hasNext())
		{
			TimeTableEntry e = data.next();
			if (e.isValid())
			{
				if (e.getStart().length() > max_aanvang) 			max_aanvang = e.getStart().length();
				if (e.getDuration().length() > max_duur) 				max_duur = e.getDuration().length();
				if (e.getInstructor().length() > max_docent) 			max_docent = e.getInstructor().length();
				if (e.getGroup().length() > max_klas)				max_klas = e.getGroup().length();
				if (e.getCourse().length() > max_cursusnaam) 	max_cursusnaam = e.getCourse().length();
				if (e.getRoom().length() > max_lokaal) 			max_lokaal = e.getRoom().length();
			} else
			{
				if (e.getErrors().length > max_error) max_error = e.getErrors().length;
			}
				
		}
		
		//System.out.println("Length of Fields:\n"
		//		+ "Maximum Aanvang: " + max_aanvang + "\n"
		//		+ "Maximum Duur: " + max_duur + "\n"
		//		+ "Maximum Docent: " + max_docent + "\n"
		//		+ "Maximum Cursusnaam: " + max_cursusnaam + "\n"
		//		+ "Maximum Klas: " + max_klas + "\n"
		//		+ "Maximum Lokaal: " + max_lokaal + "\n"
		//		+ "Maximum Dag: " + max_dag
		//);
		
		if (max_error==0) max_error++;
		
		this.createTimeTable(max_duur, max_aanvang, max_docent, max_cursusnaam, max_klas, max_lokaal, max_error, max_filepath, max_lijn);
	}
	
	public void createTimeTable() throws SQLException, DriverNotFoundException, InterfaceNotFoundException
	{
		createTimeTable(DEFAULT_DUUR_SIZE, DEFAULT_AANVANG_SIZE, DEFAULT_DOCENT_SIZE, DEFAULT_CURSUSNAAM_SIZE, DEFAULT_KLAS_SIZE, DEFAULT_LOKAAL_SIZE, DEFAULT_MAX_ERROR, DEFAULT_MAX_FILEPATH, DEFAULT_MAX_LIJN);
	}
	
	private void createTimeTable(int max_duur, int max_aanvang, int max_docent, int max_cursusnaam, int max_klas, int max_lokaal, int max_error, int max_filepath, int max_lijn) throws SQLException, DriverNotFoundException, InterfaceNotFoundException
	{
		openConnection();
		String query = "CREATE TABLE " + this.timetableName + " (\n" +
			DataField.ID 			+ " " + sqlInterface.getPKType() + " " + sqlInterface.getPKExtraOps() + ",\n" +
			DataField.START 		+ " " + sqlInterface.getStringType() + "("	+ max_aanvang 		+ ") DEFAULT NULL,\n" +
			DataField.DURATION 		+ " " + sqlInterface.getStringType() + "("	+ max_duur 			+ ") DEFAULT NULL,\n" +
			DataField.INSTRUCTOR 	+ " " + sqlInterface.getStringType() + "("	+ max_docent		+ ") DEFAULT NULL,\n" +
			DataField.COURSE		+ " " + sqlInterface.getStringType() + "("	+ max_cursusnaam 	+ ") DEFAULT NULL,\n" +
			DataField.CLASS 		+ " " + sqlInterface.getStringType() + "("	+ max_klas			+ ") DEFAULT NULL,\n" +
			DataField.CLASSROOM		+ " " + sqlInterface.getStringType() + "("	+ max_lokaal		+ ") DEFAULT NULL,\n" +
			DataField.DAY			+ " " + sqlInterface.getIntType(1)   + " DEFAULT NULL,\n" +
			DataField.FILE_NAME		+ " " + sqlInterface.getStringType() + "("	+ max_filepath		+ ") DEFAULT NULL,\n" +
			DataField.VALID_ENTRY	+ " " + sqlInterface.getCharType()   + "("	+ 1					+ ") DEFAULT 't',\n" +
			DataField.ERROR_MESSAGE + " " + sqlInterface.getTinyTextType() + ",\n" +
			DataField.FILE_LINE		+ " " + sqlInterface.getIntType(max_lijn) + " DEFAULT NULL,\n" +
				sqlInterface.getPKStatement() + "(" + DataField.ID + ")\n" +
			") " + sqlInterface.getExtraCreationOptions() + ";";

		Statement s = con.createStatement();
		//System.out.println(query);
		s.executeUpdate(query);
		closeConnection();
	}
	
	public void dropTimeTable() throws SQLException, DriverNotFoundException, InterfaceNotFoundException
	{
		this.openConnection();
		String query = "DROP TABLE IF EXISTS " + this.timetableName + ";";
		con.createStatement().executeUpdate(query);
		this.closeConnection();
	}
	
	public void emptyTable() throws SQLException, DriverNotFoundException, InterfaceNotFoundException
	{
		this.openConnection();
		Statement s = con.createStatement();
		s.executeUpdate(sqlInterface.getEmptyTableQuery() + " " + this.timetableName + " " + sqlInterface.getEmptyTableOps() + ";");
		String query2 = sqlInterface.getResetIndexQuery();
		if (!query2.equals(""))
			s.executeUpdate(sqlInterface.getAlterTableQuery() + " " + this.timetableName + " AUTO_INCREMENT=1;");
		this.closeConnection();
	}
	
	public void fillTimeTable(TimeTableDataFile data) throws SQLException, DriverNotFoundException, InterfaceNotFoundException
	{
		int size = data.getEntryCount();
		int done = 0;
		
		openConnection();
		String query = "";
		data.reset();
		while (data.hasNext())
		{
			TimeTableEntry e = data.next();
			Statement stmt = con.createStatement();
			if (e.isValid())
			{
			query = "INSERT INTO " + this.timetableName + " (" 	
																+ DataField.START + "," 
																+ DataField.DURATION + "," 
																+ DataField.INSTRUCTOR + "," 
																+ DataField.COURSE + "," 
																+ DataField.CLASS + "," 
																+ DataField.CLASSROOM + "," 
																+ DataField.DAY + ","
																+ DataField.VALID_ENTRY + ","
																+ DataField.FILE_LINE + ","
																+ DataField.FILE_NAME
																+ ") \nVALUES ('"
					+ e.getStart() + "', '"
					+ e.getDuration() + "', '"
					+ e.getInstructor().replaceAll("'", sqlInterface.escapeChar("'")) + "', '"
					+ e.getCourse().replaceAll("'", sqlInterface.escapeChar("'")) + "', '"
					+ e.getGroup().replaceAll("'", sqlInterface.escapeChar("'")) + "', '"
					+ e.getRoom().replaceAll("'", sqlInterface.escapeChar("'")) + "', '"
					+ e.getDay() + "', "
					+ "'t'" + ",'"
					+ e.getID() + "','"
					+ e.getFilename()
					+ "');";
			
			}
			else
			{
				query = "INSERT INTO " + this.timetableName + " (" 	+ DataField.ERROR_MESSAGE + ","
																	+ DataField.FILE_NAME + ","
																	+ DataField.FILE_LINE + ","
																	+ DataField.VALID_ENTRY 
																	+ ") VALUES ('" + e.getError() + "','"
																	+ e.getFilename() + "','"
																	+ e.getID() +	
																		"','f');";
			}
			//System.out.println(query);
			stmt.executeUpdate(query);
			done++;
			//System.out.println(new Integer( (int) ( (((double) done) / ((double) size)) * 100 )))
			notifyObservers(new Integer( (int) ( (double) done / (double) size * 100 )));
		}
		closeConnection();
	}
	
	private List<TimeTableEntry> getRecordsFromResultSet(ResultSet r) throws SQLException
	{
		Vector<TimeTableEntry> records = new Vector<TimeTableEntry>();
		while (r.next())
		{
			TimeTableEntry e = new TimeTableEntry();
			e.setID(Integer.parseInt(r.getString(DataField.ID)));
			e.setFilename(r.getString(DataField.FILE_NAME));
			e.setLine(r.getInt(DataField.FILE_LINE));
			if (r.getString(DataField.VALID_ENTRY).equals("t"))
			{
				e.setStart(r.getString(DataField.START));
				e.setDuration(r.getString(DataField.DURATION));
				e.setInstructor(r.getString(DataField.INSTRUCTOR));
				e.setCourse(r.getString(DataField.COURSE));
				e.setGroup(r.getString(DataField.CLASS));
				e.setDay(r.getInt(DataField.DAY));
				e.setRoom(r.getString(DataField.CLASSROOM));
			}
			else
			{
				e.setError(r.getString(DataField.ERROR_MESSAGE));
			}
			records.add(e);
		}
		return records;
	}
	
	private List<TimeTableEntry> getRecordsByColumn(String column, String value, String day)
	{
		List<TimeTableEntry> records = null;
		//System.out.println(query);
		try {
			this.openConnection();
			Statement s = con.createStatement();
			String query = "SELECT * " + " FROM " + this.timetableName + " WHERE " + column + " " 
				+ sqlInterface.getCaseInsensitiveLike() + " '%" 
				+ value.replaceAll("'", sqlInterface.escapeChar("'")) + "%' AND " 
				+ DataField.VALID_ENTRY + "='t'";
			if (day != null) query += " AND "
				+ DataField.DAY + "='" + day + "'";
			query += " ORDER BY " 
					+ DataField.DAY + ", " + DataField.START + ";";
			ResultSet r = s.executeQuery(query);
			records = getRecordsFromResultSet(r);
			
			this.closeConnection();
		} catch (DriverNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (InterfaceNotFoundException e) {
			e.printStackTrace();
		}
		return records;
	}
	
	public List<TimeTableEntry> getRecordsByDocent(String docent, String dag)
	{
		return getRecordsByColumn(DataField.INSTRUCTOR, docent, dag);
	}
	
	public List<TimeTableEntry> getRecordsByKlas(String klas, String dag)
	{
		return getRecordsByColumn(DataField.CLASS, klas, dag);
	}
	
	public List<TimeTableEntry> getRecordsByVak(String vak, String dag)
	{
		return getRecordsByColumn(DataField.COURSE, vak, dag);
	}
	
	public List<TimeTableEntry> getErrors()
	{
		List<TimeTableEntry> records = null;
		String query = "SELECT * FROM " + this.timetableName + " WHERE " + DataField.VALID_ENTRY + "='f';"; 
		
		try {
			this.openConnection();
			Statement s = con.createStatement();
			
			ResultSet r = s.executeQuery(query);
			records = getRecordsFromResultSet(r);
			
			this.closeConnection();
		} catch (DriverNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (InterfaceNotFoundException e) {
			e.printStackTrace();
		}
		return records;
	}
}

